﻿/*-----------------------------------------------------------------------------------------
   Author               Date            Description
   ----------------------------------------------------------------------------------------
   Steven Applegate     01/30/2011      Initial creation
 ----------------------------------------------------------------------------------------*/

using System;
using System.Web;
using System.Data;
using System.Drawing;
using OfficeOpenXml;
using OfficeOpenXml.Style;

namespace SuddenDeath.Domain.Services.Export
{
    public class Excel
    {
        /// <summary>
        /// Author: Steven Applegate (05/09/2011)
        /// Description: Exports a data table to an Excel file
        /// </summary>
        public static void Export(DataTable data, string worksheetName)
        {
            using (var pck = new ExcelPackage())
            {
                // Create the worksheet
                ExcelWorksheet ws = pck.Workbook.Worksheets.Add("SuddenDeath");

                // Load the datatable into the sheet, starting from cell A5. Print the column names on row 1
                ws.Cells["A5"].LoadFromDataTable(data, true);

                // put logo in header
                using (ExcelRange rng = ws.Cells[1, 1, 1, Math.Max(5, data.Columns.Count)])
                {
                    rng.Merge = true;
                    
                    Image img = Image.FromFile(HttpContext.Current.Server.MapPath("~/content/images/logo-ncpa-spreadsheet.jpg"));
                    ws.Row(1).Height = 65;
                    ws.Drawings.AddPicture("Logo", img);
                }

                // add created by text
                using (ExcelRange rng = ws.Cells[2, 1, 2, Math.Max(5, data.Columns.Count)])
                {
                    rng.Merge = true;
                    rng.Style.Fill.PatternType = ExcelFillStyle.Solid;
                    rng.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(79, 129, 189));
                    rng.Style.Font.Color.SetColor(Color.White);
                    rng.Style.Font.Size = 10;
                    rng.Style.Font.Italic = true;
                    rng.Value = "Created by: " + HttpContext.Current.User.Identity.Name;
                }

                // add date created text
                using (ExcelRange rng = ws.Cells[3, 1, 3, Math.Max(5, data.Columns.Count)])
                {
                    rng.Merge = true;
                    rng.Style.Fill.PatternType = ExcelFillStyle.Solid;
                    rng.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(79, 129, 189));
                    rng.Style.Font.Color.SetColor(Color.White);
                    rng.Style.Font.Size = 10;
                    rng.Style.Font.Italic = true;
                    rng.Value = "Date created: " + DateTime.Now.ToString("MM/dd/yyyy hh:mm:ss tt");
                }

                // Format the column headers
                using (ExcelRange rng = ws.Cells[5, 1, 5, data.Columns.Count])
                {
                    rng.Style.Font.Bold = true;
                }

                // Write it back to the client
                HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                HttpContext.Current.Response.AddHeader("content-disposition", "attachment; filename=" + worksheetName + ".xlsx");
                HttpContext.Current.Response.BinaryWrite(pck.GetAsByteArray());
            }
        }
    }
}
